﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace SharePoint.Security.DocHelpers.ExportFormats
{
    public class ExcelHelper
    {
        public void CreateScanExcelFile(string excelFileFullPath)
        {
            string templateFileFullPath = string.Format("{0}\\ExportFormats\\Templates\\ContentScanResult.xlsx", Directory.GetCurrentDirectory());

            File.Copy(templateFileFullPath, excelFileFullPath, true);
        }

        public void AddSummary(string excelFileName, List<SummaryInfo> ScanSummary)
        {
            UInt32Value style = 0U;
            uint rowIndex = 2;
            string id = null;
            WorksheetPart worksheetPart = null;
            SheetData sheetData = null;

            using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(excelFileName, true))
            {
                id = myWorkbook.WorkbookPart.Workbook.Descendants<Sheet>().First(s => s.Name == "Summary").Id;
                worksheetPart = (WorksheetPart)myWorkbook.WorkbookPart.GetPartById(id);
                sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

                //Get row index to append the row at the end of the file
                Row lastRow = sheetData.Elements<Row>().LastOrDefault();

                if (lastRow != null)
                {
                    rowIndex = lastRow.RowIndex + 1;
                }

                foreach (var scanData in ScanSummary)
                {
                    var row = new Row
                    {
                        RowIndex = (UInt32)rowIndex
                    };

                    Cell patternNameCell = CreateExcelCell(rowIndex, "A", scanData.Key, style);
                    row.AppendChild(patternNameCell);

                    Cell matchCount = CreateExcelCell(rowIndex, "B", scanData.Value, style);
                    row.AppendChild(matchCount);

                    sheetData.AppendChild(row);
                    rowIndex = rowIndex + 1;
                }
            }
        }

        public void AddPatternSummary(string excelFileName, List<SummaryInfo> ScanPatternSummary)
        {
            UInt32Value style = 0U;
            uint rowIndex = 2;
            string id = null;
            WorksheetPart worksheetPart = null;
            SheetData sheetData = null;

            using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(excelFileName, true))
            {
                id = myWorkbook.WorkbookPart.Workbook.Descendants<Sheet>().First(s => s.Name == "Pattern Summary").Id;
                worksheetPart = (WorksheetPart)myWorkbook.WorkbookPart.GetPartById(id);
                sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

                //Get row index to append the row at the end of the file
                Row lastRow = sheetData.Elements<Row>().LastOrDefault();

                if (lastRow != null)
                {
                    rowIndex = lastRow.RowIndex + 1;
                }

                foreach (var scanData in ScanPatternSummary)
                {
                    var row = new Row
                    {
                        RowIndex = (UInt32)rowIndex
                    };

                    Cell patternNameCell = CreateExcelCell(rowIndex, "A", scanData.Key, style);
                    row.AppendChild(patternNameCell);

                    Cell matchCount = CreateExcelCell(rowIndex, "B", scanData.Value, style);
                    row.AppendChild(matchCount);

                    sheetData.AppendChild(row);
                    rowIndex = rowIndex + 1;
                }
            }
        }

        public void AddErrors(string excelFileName, IEnumerable<ErrorInfo> errors)
        {
            //var ruleDatas = ruleData as RuleData[] ?? ruleData.ToArray();
            UInt32Value style = 0U;
            uint rowIndex = 2;
            string id = null;
            WorksheetPart worksheetPart = null;
            SheetData sheetData = null;

            using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(excelFileName, true))
            {
                id = myWorkbook.WorkbookPart.Workbook.Descendants<Sheet>().First(s => s.Name == "Errors").Id;
                worksheetPart = (WorksheetPart)myWorkbook.WorkbookPart.GetPartById(id);
                sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

                //Get row index to append the row at the end of the file
                Row lastRow = sheetData.Elements<Row>().LastOrDefault();

                if (lastRow != null)
                {
                    rowIndex = lastRow.RowIndex + 1;
                }

                foreach (var data in errors)
                {
                    var row = new Row
                    {
                        RowIndex = (UInt32)rowIndex
                    };

                    Cell fileNameCell = CreateExcelCell(rowIndex, "A", data.Message, style);
                    row.AppendChild(fileNameCell);

                    sheetData.AppendChild(row);
                    rowIndex = rowIndex + 1;
                }
            }

        }

        public void AddResults(string excelFileName, IEnumerable<ScanResult> results)
        {
            //var ruleDatas = ruleData as RuleData[] ?? ruleData.ToArray();
            UInt32Value style = 0U;
            uint rowIndex = 2;
            string id = null;
            WorksheetPart worksheetPart = null;
            SheetData sheetData = null;


            using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(excelFileName, true))
            {
                id = myWorkbook.WorkbookPart.Workbook.Descendants<Sheet>().First(s => s.Name == "Scan Results").Id;
                worksheetPart = (WorksheetPart)myWorkbook.WorkbookPart.GetPartById(id);
                sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

                //Get row index to append the row at the end of the file
                Row lastRow = sheetData.Elements<Row>().LastOrDefault();
                if (lastRow != null)
                {
                    rowIndex = lastRow.RowIndex + 1;
                }

                foreach (var data in results)
                {
                    var row = new Row
                    {
                        RowIndex = (UInt32)rowIndex
                    };

                    Cell fileNameCell = CreateExcelCell(rowIndex, "A", data.FileFullName, style);
                    row.AppendChild(fileNameCell);

                    Cell matchCell = CreateExcelCell(rowIndex, "B", data.MatchingContent, style);
                    row.AppendChild(matchCell);

                    Cell patternCell = CreateExcelCell(rowIndex, "C", data.Pattern, style);
                    row.AppendChild(patternCell);

                    Cell fileTypeCell = CreateExcelCell(rowIndex, "D", data.FileType, style);
                    row.AppendChild(fileTypeCell);
                   
                    Cell permCell = CreateExcelCell(rowIndex, "E", data.Permissions, style);
                    row.AppendChild(permCell);

                    Cell moreInfoCell = CreateExcelCell(rowIndex, "F", data.MoreInfo, style);
                    row.AppendChild(moreInfoCell);
                    
                    sheetData.AppendChild(row);
                    rowIndex = rowIndex + 1;
                }
            }
           
        }

        private static Cell CreateExcelCell(uint rowIndex, string columnConstant, string cellText, UInt32Value style)
        {
            var contentCell = new Cell
            {
                DataType = CellValues.InlineString,
                StyleIndex = style,
                CellReference = columnConstant + rowIndex.ToString(CultureInfo.InvariantCulture)
            };

            var contentCellInlineStr = new InlineString();
            var contentCellTxt = new Text
            {
                Text = cellText,
                Space = SpaceProcessingModeValues.Preserve
            };

            contentCellInlineStr.AppendChild(contentCellTxt);
            contentCell.AppendChild(contentCellInlineStr);
            return contentCell;
        }

    }
}
